package com.pz.cloud.framework.util;

import com.pz.cloud.framework.base.expection.CustomRuntimeException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel模板导出的工具类
 */
public class JxlsUtils {
    private final static Logger LOG = LoggerFactory.getLogger(JxlsUtils.class);

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model){
        try {
            Context context = PoiTransformer.createInitialContext();
            if (model != null) {
                for (String key : model.keySet()) {
                    context.putVar(key, model.get(key));
                }
            }
            JxlsHelper jxlsHelper = JxlsHelper.getInstance();
            Transformer transformer  = jxlsHelper.createTransformer(is, os);
            //获得配置
            JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
            //函数强制，自定义功能
            Map<String, Object> funcs = new HashMap<String, Object>();
            funcs.put("utils", new JxlsUtils());    //新增自定义功能
            evaluator.getJexlEngine().setFunctions(funcs);
            //必须要这个，否者表格函数统计会错乱
            jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
        } catch (IOException e) {
            LOG.error("导出excel失败 ",e);
        }finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    LOG.error("关闭输入流失败", e);
                }
            }
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    LOG.error("关闭输出流失败", e);
                }
            }
        }
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) {
        File template = getTemplate(templatePath);
        if(template != null){
            try {
                exportExcel(new FileInputStream(template), os, model);
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }
        } else {
            throw new CustomRuntimeException("Excel 模板未找到。");
        }
    }

    //获取jxls模版文件
    public static File getTemplate(String path){
        File template = new File(path);
        if(template.exists()){
            return template;
        }
        return null;
    }
    
    public static OutputStream getOutputStream(String fileName, HttpServletRequest request, HttpServletResponse response) {
        ServletOutputStream outputStream;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + JxlsUtils.getDownloadFileName(fileName, request));
            response.setContentType("application/vnd.ms-excel");
            outputStream  = response.getOutputStream();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return outputStream;
    }

    private static String getBrowserName(String agent) {
        if(agent.indexOf("msie 7")>0){
            return "ie7";
        }else if(agent.indexOf("msie 8")>0){
            return "ie8";
        }else if(agent.indexOf("msie 9")>0){
            return "ie9";
        }else if(agent.indexOf("msie 10")>0){
            return "ie10";
        }else if(agent.indexOf("msie")>0){
            return "ie";
        }else if(agent.indexOf("opera")>0){
            return "opera";
        }else if(agent.indexOf("firefox")>0){
            return "firefox";
        }else if(agent.indexOf("webkit")>0){
            return "webkit";
        }else if(agent.indexOf("gecko")>0 && agent.indexOf("rv:11")>0){
            return "ie11";
        }else{
            return "Others";
        }
    }

    /**
     * 获取模板文件名，使用多种浏览器防止乱码
     * @param fileName
     * @param request
     * @return
     * @throws Exception
     */
    public static String getDownloadFileName(String fileName, HttpServletRequest request) throws Exception {
        String userAgent = getBrowserName(request.getHeader("USER-AGENT").toLowerCase());
        String downloadFile = null;
        if (userAgent.contains("firefox")) {//火狐浏览器
            downloadFile = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
        } else {
            downloadFile = URLEncoder.encode(fileName, "UTF8");//其他浏览器
        }
        return downloadFile;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

    /**
     * 提取【导入】的数据列表
     *
     * @param modelFilePath
     *            数据导入时对应的数据模板文件
     * @param stream
     *            用户上传时的流文件
     * @return
     * @throws IOException
     */
    public static List<Map<String, String>> getDataList(String modelFilePath, InputStream stream)
            throws IOException {
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        FileInputStream fi = null;
        Workbook hwb = null;
        Row row = null;
        Cell cell = null;
        HashMap<String, String> cellField = new HashMap<String, String>();
        int dataRowStart = 0;
        // 获取绑定及对应列位置
        try {
            fi = new FileInputStream(modelFilePath);
            hwb = WorkbookFactory.create(fi);
            Sheet sheet = hwb.getSheetAt(0);
            int rows = sheet.getLastRowNum();
            int cols = -1;
            for (int i = 0; i <= rows; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                cols = row.getLastCellNum();
                for (int j = 0; j < cols; j++) {
                    cell = row.getCell(j);
                    String value = "";
                    if (null != cell) {
                        value = cell.getRichStringCellValue().toString();
                        if (value.indexOf("${") != -1) {
                            // 数据位置
                            dataRowStart = i;
                            cellField.put("" + j,
                                    value.substring(value.indexOf("{") + 1, value.indexOf("}")));
                        }

                    }
                }
            }
            // 根据绑定的字段获取数据列表
            if (cellField.keySet() != null) {
                hwb = WorkbookFactory.create(stream);
                sheet = hwb.getSheetAt(0);
                rows = sheet.getLastRowNum();
                cols = -1;
                for (int i = dataRowStart; i <= rows; i++) {
                    row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    cols = row.getLastCellNum();
                    Map<String, String> rowData = new HashMap<String, String>();
                    // 当前行是否所有列都为空
                    boolean isAllNull = false;
                    for (int j = 0; j < cols; j++) {
                        cell = row.getCell(j);
                        String cellValue = "";
                        if (null != cell) {
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        double d = cell.getNumericCellValue();
                                        Date date = HSSFDateUtil.getJavaDate(d);
                                        SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                                        cellValue = dformat.format(date);
                                    } else {
                                        NumberFormat nf = NumberFormat.getInstance();
                                        nf.setGroupingUsed(false); // true时的格式：1,234,567,890
                                        cellValue = nf.format(cell.getNumericCellValue()); // 数值类型的数据为double，所以需要转换一下
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    cellValue = String.valueOf(cell.getCellFormula());
                                    break;

                                default:
                                    cellValue = "";
                                    break;
                            }
                            // 获取对应绑定字段（包含表名标识）
                            String fullField = cellField.get("" + j) == null ? "" : cellField.get(
                                    "" + j).toString();
                            if (!"".equals(fullField)) {
                                rowData.put(fullField, cellValue.trim());
                            }
                            if (!"".equals(cellValue)) {
                                isAllNull = true;
                            }
                        }
                    }
                    if (isAllNull) {
                        dataList.add(rowData);
                    }
                    isAllNull = false;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fi != null) {
                fi.close();
            }
        }
        return dataList;
    }
}
